﻿
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using Citiport.Util.Language;
using Citiport.Util.Google.Language;
using Citiport.Util;
using log4net;
using log4net.Config;
using System.Text;

/// <summary>
/// Utility class for database operations
/// Author: Jeff
/// Date: 04/04/2008
/// </summary>
public class DatabaseUtil
{

    public class TranslatedPost
    {
        public String text { set; get; }

        public bool isOriginal { set; get; }

        public int lang_no {set;get;}

        public TranslatedPost(String text, bool isOriginal, int lang_no)
        {
            this.text = text;
            this.isOriginal = isOriginal;
            this.lang_no = lang_no;
        }
    }

    private static readonly ILog logger = LogManager.GetLogger(typeof(DatabaseUtil));

    public static readonly String GoogleLangSeparator = "|";

    /// <summary>
    /// CAUTION: this is the key for getting origianl post form postsTable, to use OrigianlLangKey+post_no
    /// </summary>
    public static readonly Object OrigianlLangKey = "citiport.db.translatedtxt.original";

    /// <summary>
    /// Constructor - not used now
    /// </summary>
    Hashtable langTable;
	public DatabaseUtil()
	{
        langTable = new Hashtable();
	}

    /// <summary>
    /// Enumerators for from_tables to prevent the stupid error 
    /// </summary>
    public enum fromTables : int
    {
        forum_text, forum_title, intro_intro, picture_comment, s_profile_title, 
        s_review_comment, user_msg_message, ct_profile_title, ct_profile_country, 
        ct_profile_continent, s_profile_intro
    }

    /// <summary>
    /// Languages to translsate. In phrase 1, only zh-hant, zh-cn and en
    /// </summary>
    public String[] availibleLang = { 
                                 LanguageConst.CHINESE_SIMPLIFIED, 
                                 LanguageConst.CHINESE_TRADITIONAL_GOOGLE, 
                                 LanguageConst.ENGLISH 
                             };



    public int writeTranslatedTxt(int from_table_no, int post_no, String txt, Hashtable lang_table, string defaultLang)
    {
        if (txt == null)
        {
            return -1;
        }
        if (txt.Length > 500)
        {
            //throw new GoogleTranslatedLimitation(Citiport.Core.Error.LogLevel.Error, txt, Citiport.Core.Error.GoogleTranslateLimit.MaxLengthExcessed);
            logger.Error("TEXT OVER 500 TEXT LENGTH:"+txt.Length+"Table no:[" + from_table_no + "] Post no" + post_no );
            return -1;
        }
		//Find detect language code, if not find use user default
        String detectLang = null;
        JSON.DetectedLang dl = null;
        try
        {
            String json = GoogleLangAPI.DetectLang(txt);
            dl = JSON.parseDetectedLang(json);

            detectLang = dl.responseData.language; //TODO: use google detect
        }
        catch (GoogleLanguageException gle)
        {
            logger.Error("Table no:[" + from_table_no + "] Post no" + post_no + " Error url:" + gle.RequestUrl);
            logger.Error("Msg: " + gle.Message + " Status code" + gle.StatsCode);
        }
        if (detectLang != null && isResultReliable(dl))
        {
            detectLang = detectLang.ToLower().Trim();
        }
        else
        {
            detectLang = defaultLang.ToLower().Trim();
        }
        //translate txt
		for (int i = 0; i < availibleLang.Length; i++)
		{
			try
			{
                TranslatedPost tp = translateTextByGoogle(txt, detectLang, availibleLang[i], lang_table);
                writeTranslatedTextToDb(tp, post_no, from_table_no);
			}
			catch (GoogleLanguageException gle)
			{
				logger.Error("Table no:[" + from_table_no + "] Post no" + post_no + " Error url:" + gle.RequestUrl);
				logger.Error("Msg: " + gle.Message + " Status code" + gle.StatsCode);
			}
		}

        return 0;
    }

    /// <summary>
    /// Is google detect result reliable
    /// </summary>
    /// <param name="dl">detected result</param>
    /// <returns>true whether the result is reliable</returns>
    private bool isResultReliable(JSON.DetectedLang dl)
    {
        /*
         * More logic
         */
        if (dl == null)
            return false;
        return dl.responseData.isReliable;
    }

    private TranslatedPost translateTextByGoogle(String text, String  post_lang,String availible_lang, Hashtable lang_table) 
    {
		/*
		if availible_lang == post_lang, then no need to translated
		
		if languagePair is valid,
			then translate
		else 
			XX=>english then english=>YY
			
		if status == 400
			throws exception
		
		*/
		int int_avalible_lang = getLanguageNo(availible_lang.ToLower().Trim(), lang_table);
		int int_post_lang = getLanguageNo(post_lang.ToLower().Trim(), lang_table);
		if(int_avalible_lang == int_post_lang)
		{
			TranslatedPost tp = new TranslatedPost(text, true, int_post_lang);
			return tp;
		}
			
        JSON.TranslatedText tt = translateByToken(text, post_lang+GoogleLangSeparator + availible_lang);
        String resultTxt = tt.responseData.translatedText;
        if (tt.responseStatus == 400)
        {
			/*
				if invalid pair
					translate to english first for now
					if still not able to translate, return origianl for now
			*/
			//throws exception here
            logger.Fatal(tt.responseDetails);
            resultTxt = text;
        }
		
        TranslatedPost tlp = new TranslatedPost(resultTxt, false, int_avalible_lang);
        return tlp; 
    }

    private Boolean isOriginalLang(String detect, String availiable, Hashtable lang_table)
    {
        int x = getLanguageNo(detect.ToLower().Trim(), lang_table);
        int y = getLanguageNo(availiable.ToLower().Trim(), lang_table);
        return (x==y) ;
    }

    private void writeTranslatedTextToDb(TranslatedPost tp , int post_no , int from_table) 
    {
        ctpDataContext db = new ctpDataContext();
        ct_language_text translatedTxt = new ct_language_text();
        //Get lang_no from lang_table
        translatedTxt.language_no = tp.lang_no;
        translatedTxt.from_table_no = from_table;
        translatedTxt.post_no = post_no;
        translatedTxt.text = tp.text;
        translatedTxt.is_original = tp.isOriginal;
        db.ct_language_texts.InsertOnSubmit(translatedTxt);
        db.SubmitChanges();
    }

    private JSON.TranslatedText translateByToken(String txt, String langs)
    {
        String json = GoogleLangAPI.TranslateText(txt, langs);
        JSON.TranslatedText tt = JSON.parseTranslatedText(json);
        return tt;
    }

    /* Translate by token
    private JSON.TranslatedText translateByToken(String txt, String langs)
    {
        String holder = txt;
        int length = txt.Length;
        JSON.TranslatedText tt =null;
        //cell length
        int tokenLength = length / GoogleLangAPI.GOOGLE_LANG_TEXT_MAXLENGTH +1;
        ArrayList tokens = new ArrayList(tokenLength);

        int startIndex = 0;
        int strLength = GoogleLangAPI.GOOGLE_LANG_TEXT_MAXLENGTH;
        for (int i = 0; i < tokenLength; i++)
        {
            tokens.Add(holder.Substring(startIndex, strLength));
            startIndex = (i + 1) * strLength;
            if (startIndex + strLength > length)
                strLength = length - startIndex - 1;
        }
        
        
        IEnumerator en = tokens.GetEnumerator();
        StringBuilder sb = new StringBuilder();
        while (en.MoveNext())
        {
            if (en.Current != null)
            {
                String json = GoogleLangAPI.TranslateText(en.Current.ToString(), langs);
                tt = JSON.parseTranslatedText(json);
                sb.Append(tt.responseData.translatedText);
            }
        }
        tt.responseData.translatedText = sb.ToString();
        return tt;

    }
    */

    /// <summary>
    /// See above
    /// </summary>
    /// <param name="from_table_no"></param>
    /// <param name="post_no"></param>
    /// <param name="txt"></param>
    /// <param name="lang_table"></param>
    /// <returns></returns>
    public int writeTranslatedTxt(int from_table_no, int post_no, String txt, Hashtable lang_table)
    {
        return this.writeTranslatedTxt(from_table_no, post_no, txt, lang_table, "en");
    }

    /// <summary>
    /// See above
    /// </summary>
    /// <param name="from_table_no"></param>
    /// <param name="post_no"></param>
    /// <param name="txt"></param>
    /// <param name="defaultLang"></param>
    /// <returns></returns>
    public int writeTranslatedTxt(int from_table_no, int post_no, String txt, String defaultLang)
    {
        return this.writeTranslatedTxt(from_table_no, post_no, txt, this.getLangTable(), defaultLang);
    }

    /// <summary>
    /// Get Language No from its corresponding language code
    /// </summary>
    /// <param name="lang_code">language code - en,zh-tw</param>
    /// <returns>The corresponding language code which is referred to database</returns>
    public int getLanguageNo(String lang_code)
    {
        Hashtable langTable = getLangTable();
        return this.getLanguageNo(lang_code,langTable);
    }

    public int getLanguageNo()
    {
        GeoLingManager glManager = new GeoLingManager(null, null);
        //Get user's preferred langugage in int
        return this.getLanguageNo(glManager.getLang());
    }


    public int getLanguageNo(String lang_code, Hashtable langTable)
    {
        Object result = langTable[lang_code.ToLower()];
        //IF language not found return 0
        if (result == null)
        {
            return 0;
        }
        else
        {
            return DataUtil.OjectToInt(result);
        }
    }

    /// <summary>
    /// Get language table from database to hashtable
    /// Note:
    /// The key is language code in lower case. EX: en and zh-tw
    /// </summary>
    /// <returns>Hashtable which contains the language no and code</returns>
    public Hashtable getLangTable()
    {
        Hashtable langTable = new Hashtable();
        ctpDataContext db = new ctpDataContext();
        var langs = (from p in db.languages select new { code = p.code, no = p.no }).Distinct();
        foreach (var lang in langs)
        {
            /*
             * zh => simplified chinese
             * zh-tw => traditional chinese
             * in DB
             */
            //lower characters to fix zh-CN issue
            String code = lang.code.ToLower().Trim();
            langTable.Add(code, lang.no);
            //Add zh-cn to fix the zh issue
            if (code.Equals("zh"))
            {
                langTable.Add("zh-cn", lang.no);
            }
            //Ad zh-hant to fix the google detect issue
            if (code.EndsWith("zh-tw"))
            {
                langTable.Add("zh-hant", lang.no);
            }
        }
        return langTable;
    }

    /// <summary>
    /// Get the post which is based on post_no and lang_no
    /// </summary>
    /// <param name="original_txt">The text in original language</param>
    /// <param name="table">Hashtable which contains all the translated texts</param>
    /// <param name="post_no">The post no</param>
    /// <param name="lang_no">The language no EX: 1</param>
    /// <returns>Text</returns>
    public String getPropertyPost(String original_txt, Hashtable table, int post_no, String lang_no)
    {
        TranslatedPost txt = (TranslatedPost)table[LangHashKey(post_no.ToString(), lang_no)];
        if(txt == null)
            return original_txt;
        return txt.text;
    }

    /// <summary>
    /// TODO
    /// </summary>
    /// <param name="lang"></param>
    /// <param name="table"></param>
    /// <param name="post_no"></param>
    /// <returns></returns>
    public String getOriginalLang(Hashtable table, Hashtable langTable, int post_no, String _lang)
    {
        IDictionaryEnumerator en = table.GetEnumerator();
        while (en.MoveNext())
        {
            String key = DataUtil.ObjetToString(en.Key);
            if (this.isMatchedPost(key, post_no))
            {
                TranslatedPost value = (TranslatedPost)en.Value;
                if (value.isOriginal)
                {
                    if( DataUtil.ObjetToString(value.lang_no).Trim().ToLower().Equals(_lang.ToLower().Trim()))
                        return null;
                    Object r = langTable[value.lang_no];
                    if (r == null)
                        return null;
                    else
                    {
                        String l = DataUtil.ObjetToString(r);
                        return OriginalLangFormat(l);
                    }
                }
            }
        }
        return null;
    }

    private String OriginalLangFormat(string lang)
    {
        StringBuilder sb = new StringBuilder("[");
        sb.Append(Resources.Common.original_text);
        sb.Append(" : " + lang + "]");
        return sb.ToString();
    }

    public Hashtable getLanguageTable(String _lang)
    {
        ctpDataContext db = new ctpDataContext();
        var l = from lang in db.languages
                where lang.language_no == DataUtil.StringToInt(_lang)
                select new { lang.no, lang.title };
        Hashtable h = new Hashtable();
        foreach (var x in l)
        {
            h.Add(x.no, x.title);
        }
        return h;
    }

    /// <summary>
    /// Get the raw post which is based on post_no
    /// </summary>
    /// <param name="original_txt"></param>
    /// <param name="table">Hashtable which contains all the translated text</param>
    /// <param name="post_no">The post no</param>
    /// <returns>the raw post</returns>
    public String getOriginalPost(String original_txt, Hashtable table, int post_no)
    {
        IDictionaryEnumerator en = table.GetEnumerator();
        while (en.MoveNext())
        {
            String key = DataUtil.ObjetToString(en.Key);
            if (this.isMatchedPost(key, post_no))
            {
                TranslatedPost value = (TranslatedPost)en.Value;
                if (value.isOriginal)
                    return value.text;
            }
        }
        return original_txt;
    }

    public String getPropertyPost(String original_txt, int from_table, int post_no, String lang_no)
    {
        int[] post_nos = {post_no};
        Hashtable table = getTranlatePosts(from_table, post_nos);
        return this.getPropertyPost(original_txt, table, post_no, lang_no);
    }

    /// <summary>
    /// Get transalted posts
    /// </summary>
    /// <param name="from_table_col">The number which is represents the column of table. EX: 4=>ct_s_profile.title </param>
    /// <param name="post_nos">The numbers which represent the post no. EX 34,21,232 in an int array</param>
    /// <returns>Hashtable which contains all the translated texts see: getPropertyPost</returns>
    public Hashtable getTranlatePosts(int from_table_col, int[] post_nos)
    {
        ctpDataContext db = new ctpDataContext();
        
        Hashtable dt = new Hashtable();
        var posts = (from tran in db.ct_language_texts
                    where tran.from_table_no == from_table_col && post_nos.Contains(tran.post_no)
                    select tran).Distinct();
        int len = posts.Count();
        foreach (var post in posts)
        {
            
            String key = LangHashKey(post.post_no.ToString() , post.language_no.ToString());
            if (!dt.ContainsKey(key))
            {
                TranslatedPost value = new TranslatedPost(post.text, Convert.ToBoolean(post.is_original), post.language_no);
                dt.Add(key, value);
            }
        }
        return dt;
    }

    public static char LANG_HASHKEY_SEPARATOR = '-';

    public static String LangHashKey(String post_no, String language_no)
    {
        return post_no + LANG_HASHKEY_SEPARATOR + language_no;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="langHashKey"></param>
    /// <param name="post_no"></param>
    /// <returns></returns>
    public bool isMatchedPost(String langHashKey, int post_no)
    {
        String[] s = langHashKey.Split(LANG_HASHKEY_SEPARATOR);
        if (s.Length > 0)
        {
            return (post_no == DataUtil.StringToInt(s[0]));
        }

        return false;
    }

    /// <summary>
    /// Update the text in ct_language_text.text to most recent and save the historial text to ct_text_history
    /// </summary>
    /// <param name="from_table">Refer to DatabaseUtil.from_table</param>
    /// <param name="post_no">post no</param>
    /// <param name="new_text">text to update</param>
    /// <param name="lang">language no</param>
    /// <returns>Rrtuen true wether the update is successed</returns>
    public bool writeTranslatedTextToHistory(int from_table, int post_no, String new_text, int lang)
    {
        /*
         *NOTICE:
         *
         * ct_language_text keeps the newest text. previous text is going to be written to ct_text_history
         * 
         * 
         * SUDO:
         * Read from ct_language_text with from_table_no and post_no
         * if not existed, return false
         * else
         * get text and no
         * replace ct_language_text.text with text
         * 
         * get priority version no
         * write time, version, from_table, post_no, ct_text_no, text to ct_text_history
         * 
         */
        ctpDataContext db = new ctpDataContext();
        var oldposts = from olds in db.ct_language_texts 
                   where olds.from_table_no == from_table && olds.post_no == post_no && olds.language_no == lang
                   select olds;
        int count = oldposts.Count();
        if (count < 1 || count > 1)
        {
            //logger.Error("The post is not unique");
            return false; //bug
        }
        ct_language_text old = oldposts.First();
        String old_text = old.text;
        old.text = new_text;
        if (old_text.Equals(new_text))
            return false;
        //commit data to ct_language_text
     
        //Write to ct_text_history
        int ct_language_text_no = old.no;
        int? new_ver = -1;
        var t = db.ct_text_histories.Where(x => x.ct_language_text_no == ct_language_text_no);
        if (t.Count() <= 0)
            new_ver = 0;
        else
        {
            new_ver = t.Max(x => x.ver);
            new_ver++;
        }
        ct_text_history history = new ct_text_history();
        history.text = old_text;
        history.wrote_timestamp = DateTime.Now;
        history.post_no = post_no;
        history.from_table_no = from_table;
        history.ct_language_text_no = ct_language_text_no;
        history.language_no = lang;
        history.ver = new_ver.Value;
        db.ct_text_histories.InsertOnSubmit(history);
        try
        {
            db.SubmitChanges();
        }
        catch (Exception ex)
        {
            logger.Fatal("Fatal Error when updating ct_language_text: "+ ex.ToString());
            throw ex;
        }
        //Wrote successfully
        return true;
    }
}
